--------------------------------------------------------------------------------
-- @name: table full scan lru
-- @author: dion cho
-- @note: test case for table full scan and lru list
--------------------------------------------------------------------------------

set echo on
set pages 10000
set lines 200
set timing off
set trimspool on
set serveroutput off


@capture_on

------------------------------------------------------------
-- Case 1: table full scan and lru list

-- set db cache size 100m
--alter system set sga_target=0m scope=spfile;
--alter system set shared_pool_size=100m scope=spfile;
--alter system set db_cache_size=100m scope=spfile;
--
--connect /as sysdba
--shutdown abort
--startup
--

show sga

@para _small_table_threshold
col value new_value buffers
@para _db_block_buffers


drop table t_big purge;
drop table t_big_25 purge;
drop table t_small_25 purge;

-- table bigger than buffer cache to fill the buffer cache
create table t_big
as
select
	rpad('x', 2000) as c1,
	rpad('x', 2000) as c2,
	rpad('x', 2000) as c3
from dual
connect by level <= (&buffers - 100);


-- table a bit bigger that 25% of buffer_cache 
create table t_big_25 
as
select
	rpad('x', 2000) as c1,
	rpad('x', 2000) as c2,
	rpad('x', 2000) as c3
from dual
connect by level <= (0.25*&buffers + 300);


-- table a bit smaller than 25% of buffer_cache 
create table t_small_25 
as
select
	rpad('x', 2000) as c1,
	rpad('x', 2000) as c2,
	rpad('x', 2000) as c3
from dual
connect by level <= (0.25*&buffers - 300);


select segment_name, blocks, bytes 
from dba_segments
where segment_name in ('T_BIG', 'T_BIG_25', 'T_SMALL_25');


-- disable serial direct path read
alter session set events '10949 trace name context forever, level 1';

-- read table t_big to fill the buffer cache
alter system flush buffer_cache;

set autotrace traceonly stat
select count(*) from t_big;

select count(*) from t_big;

-- read table t_big_25
select count(*) from t_big_25;

select count(*) from t_big_25;

-- read table t_small_25
select count(*) from t_small_25;

select count(*) from t_small_25;

set autotrace off

alter session set events '10949 trace name context off';


----------------------------------------------------------
-- Case 2: serial direct path read
select * from table(tpack.oerr(10949));

col value new_value sth
@para _small_table_threshold

drop table t_sth;

create table t_sth
as
select
	rpad('x',2000) as c1,
	rpad('x',2000) as c2,
	rpad('x',2000) as c3,
	rpad('x',1000) as c4
from dual
connect by level <= 5*&sth + 50;


-- with 10949 disabled, which is default
alter system flush buffer_cache;

exec tpack.begin_diag_trace(userenv('sid'), 10046, 8);

select count(*) from t_sth;

exec tpack.end_diag_trace(userenv('sid'), 10046);

select * from table(tpack.get_diag_trace(userenv('sid'), 'TKPROF', 'sys=no'));

-- with 10949 enabled
alter session set events '10949 trace name context forever, level 1';

alter system flush buffer_cache;

exec tpack.begin_diag_trace(userenv('sid'), 10046, 8);

select count(*) from t_sth;

exec tpack.end_diag_trace(userenv('sid'), 10046);

select * from table(tpack.get_diag_trace(userenv('sid'), 'TKPROF'));

alter session set events '10949 trace name context off';


-- very large object
drop table t_vlot;

create table t_vlot
as
select
	rpad('x',2000) as c1,
	rpad('x',2000) as c2,
	rpad('x',2000) as c3,
	rpad('x',1000) as c4
from dual
connect by level <= 6500
;

col tsize new_value tsize
select trunc(blocks*8*1024/1024/1024) + 80 as tsize
from dba_segments
where owner = user and segment_name = 'T_VLOT'
;

alter session set "_very_large_object_threshold" = &tsize;

-- even when 10949 is enabled
alter session set events '10949 trace name context forever, level 1';

alter system flush buffer_cache;

exec tpack.begin_diag_trace(userenv('sid'), 10046, 8);

select count(*) from t_vlot;

exec tpack.end_diag_trace(userenv('sid'), 10046);

select * from table(tpack.get_diag_trace(userenv('sid'), 'TKPROF', 'sys=no'));

alter session set events '10949 trace name context off';


@capture_off

